using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// If your application directly connects to SQL Server you may consider using this class.
///
///	Examples:
/// 
///	DbUtil.ExecuteNonQuery(ConnectionString, "MyStoredProcedure",
///		new SqlParameter("ID", 1),
///		new SqlParameter("Name", "My Name")
///	);
///	
///	or
/// 
///	using(SqlDataReader reader = DbUtil.OpenDataReader(ConnectionString, "GetSomethingByID", new SqlParameter("ID", 1)))
///	{
///		while(reader.Read())
///		{
///		}
///	}
/// </summary>
public static class DbUtil
{
	public static SqlConnection OpenConnection(string connectionString)
	{
		SqlConnection conn = new SqlConnection(connectionString);
		try
		{
			conn.Open();
		}
		catch(Exception ex)
		{
			conn.Dispose();	// Make sure we dispose connection even if we got an exception
			throw ex;			// Rethrow the exception
		}
		return conn;
	}

	private static SqlCommand CreateCommand(string connectionString,
		string procedureName, params SqlParameter[] parameters)
	{
		return CreateCommand(OpenConnection(connectionString), procedureName, parameters);
	}

	public static SqlCommand CreateCommand(SqlConnection conn,
		string procedureName, params SqlParameter[] parameters)
	{
		SqlCommand cmd = new SqlCommand(procedureName, conn);
		cmd.CommandType = CommandType.StoredProcedure;
		foreach(SqlParameter parameter in parameters)
		{
			cmd.Parameters.Add(parameter);
		}
		return cmd;
	}

	private static SqlCommand CreateCommand(SqlTransaction transaction,
		string procedureName, params SqlParameter[] parameters)
	{
		SqlCommand cmd = CreateCommand(transaction.Connection, procedureName, parameters);
		cmd.Transaction = transaction;
		return cmd;
	}

	public static int ExecuteNonQuery(SqlTransaction transaction,
		string procedureName, params SqlParameter[] parameters)
	{
		return CreateCommand(transaction, procedureName, parameters).ExecuteNonQuery();
	}

	public static int ExecuteNonQuery(string connectionString,
		string procedureName, params SqlParameter[] parameters)
	{
		SqlCommand cmd = CreateCommand(connectionString, procedureName, parameters);
		try
		{
			return cmd.ExecuteNonQuery();
		}
		finally
		{
			cmd.Connection.Close();
		}
	}

    public static object ExecuteScalar(SqlTransaction transaction,
        string procedureName, params SqlParameter[] parameters)
    {
        return CreateCommand(transaction, procedureName, parameters).ExecuteScalar();
    }

	public static object ExecuteScalar(string connectionString,
		string procedureName, params SqlParameter[] parameters)
	{
		SqlCommand cmd = CreateCommand(connectionString, procedureName, parameters);
		try
		{
			return cmd.ExecuteScalar();
		}
		finally
		{
			cmd.Connection.Close();
		}
	}

	public static DataSet ExecuteDataSet(string connectionString,
		string procedureName, params SqlParameter[] parameters)
	{
		SqlCommand cmd = CreateCommand(connectionString, procedureName, parameters);
		try
		{
			DataSet ds = new DataSet();
			new SqlDataAdapter(cmd).Fill(ds);
			return ds;
		}
		finally
		{
			cmd.Connection.Close();
		}
	}

	public static SqlDataReader OpenDataReader(string connectionString,
		string procedureName, params SqlParameter[] parameters)
	{
		return OpenDataReader(connectionString, procedureName, CommandBehavior.Default, parameters);
	}

	public static SqlDataReader OpenDataReader(string connectionString,
		string procedureName, CommandBehavior behavior, params SqlParameter[] parameters)
	{
		SqlCommand cmd = CreateCommand(connectionString, procedureName, parameters);
		return cmd.ExecuteReader(CommandBehavior.CloseConnection | behavior);
	}

	public static SqlParameter CreateParameter(string paramName, SqlDbType dbType, object value)
	{
		SqlParameter parameter = new SqlParameter(paramName, dbType);
		parameter.Value = null == value ? DBNull.Value : value;
		return parameter;
	}

    public static SqlParameter CreateParameter(string paramName, object value)
    {
        SqlParameter parameter = new SqlParameter(paramName, value);
        parameter.Value = null == value ? DBNull.Value : value;
        return parameter;
    }

	public static SqlParameter CreateOutputParameter(string paramName, SqlDbType dbType)
	{
		SqlParameter parameter = new SqlParameter(paramName, dbType);
		parameter.Direction = ParameterDirection.Output;
		return parameter;
	}

	public static SqlParameter CreateInputOutputParameter(string paramName, SqlDbType dbType, object value)
	{
		SqlParameter parameter = new SqlParameter(paramName, dbType);
		parameter.Direction = ParameterDirection.InputOutput;
		parameter.Value = value;
		return parameter;
	}

	public static SqlParameter CreateReturnParameter(/*string paramName, SqlDbType dbType*/)
	{
		SqlParameter parameter = new SqlParameter("@ReturnValue", SqlDbType.Int);
		parameter.Direction = ParameterDirection.ReturnValue;
		return parameter;
	}

	#region GetString
	public static string GetString(IDataReader reader, string columnName)
	{
		return reader.GetString(reader.GetOrdinal(columnName));
	}

	public static string GetString(IDataReader reader, string columnName, string defaultValue)
	{
		return GetString(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static string GetString(IDataReader reader, int columnIndex, string defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetString(columnIndex);
	}
	#endregion

	#region GetByte/GetNullableByte
	public static byte GetByte(IDataReader reader, string columnName)
	{
		return reader.GetByte(reader.GetOrdinal(columnName));
	}

	public static byte GetByte(IDataReader reader, string columnName, byte defaultValue)
	{
		return GetByte(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static byte GetByte(IDataReader reader, int columnIndex, byte defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetByte(columnIndex);
	}

	public static byte? GetNullableByte(IDataReader reader, string columnName)
	{
		return GetNullableByte(reader, reader.GetOrdinal(columnName));
	}

	public static byte? GetNullableByte(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetByte(columnIndex);
	}
	#endregion

	#region GetInt16/GetNullableInt16
	public static short GetInt16(IDataReader reader, string columnName)
	{
		return reader.GetInt16(reader.GetOrdinal(columnName));
	}

	public static short GetInt16(IDataReader reader, string columnName, short defaultValue)
	{
		return GetInt16(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static short GetInt16(IDataReader reader, int columnIndex, short defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetInt16(columnIndex);
	}

	public static short? GetNullableInt16(IDataReader reader, string columnName)
	{
		return GetNullableInt16(reader, reader.GetOrdinal(columnName));
	}

	public static short? GetNullableInt16(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetInt16(columnIndex);
	}
	#endregion

	#region GetInt32/GetNullableInt32
	public static int GetInt32(IDataReader reader, string columnName)
	{
		return reader.GetInt32(reader.GetOrdinal(columnName));
	}

	public static int GetInt32(IDataReader reader, string columnName, int defaultValue)
	{
		return GetInt32(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static int GetInt32(IDataReader reader, int columnIndex, int defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetInt32(columnIndex);
	}

	public static int? GetNullableInt32(IDataReader reader, string columnName)
	{
		return GetNullableInt32(reader, reader.GetOrdinal(columnName));
	}

	public static int? GetNullableInt32(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetInt32(columnIndex);
	}
	#endregion

	#region GetInt64/GetNullableInt64
	public static long GetInt64(IDataReader reader, string columnName)
	{
		return reader.GetInt64(reader.GetOrdinal(columnName));
	}

	public static long GetInt64(IDataReader reader, string columnName, long defaultValue)
	{
		return GetInt64(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static long GetInt64(IDataReader reader, int columnIndex, long defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetInt64(columnIndex);
	}

	public static long? GetNullableInt64(IDataReader reader, string columnName)
	{
		return GetNullableInt64(reader, reader.GetOrdinal(columnName));
	}

	public static long? GetNullableInt64(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetInt64(columnIndex);
	}
	#endregion

	#region GetFloat/GetNullableFloat
	public static float GetFloat(IDataReader reader, string columnName)
	{
		return reader.GetFloat(reader.GetOrdinal(columnName));
	}

	public static float GetFloat(IDataReader reader, string columnName, float defaultValue)
	{
		return GetFloat(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static float GetFloat(IDataReader reader, int columnIndex, float defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetFloat(columnIndex);
	}

	public static float? GetNullableFloat(IDataReader reader, string columnName)
	{
		return GetNullableFloat(reader, reader.GetOrdinal(columnName));
	}

	public static float? GetNullableFloat(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetFloat(columnIndex);
	}
	#endregion

    #region GetFloat/GetNullableDouble
    
    public static double GetDouble(IDataReader reader, string columnName)
    {
        return reader.GetDouble(reader.GetOrdinal(columnName));
    }

    public static double GetDouble(IDataReader reader, string columnName, double defaultValue)
    {
        return GetDouble(reader, reader.GetOrdinal(columnName), defaultValue);
    }

    public static double GetDouble(IDataReader reader, int columnIndex, double defaultValue)
    {
        if (reader.IsDBNull(columnIndex))
            return defaultValue;
        return reader.GetDouble(columnIndex);
    }

    public static double? GetNullableDouble(IDataReader reader, string columnName)
    {
        return GetNullableDouble(reader, reader.GetOrdinal(columnName));
    }

    public static double? GetNullableDouble(IDataReader reader, int columnIndex)
    {
        if (reader.IsDBNull(columnIndex))
            return null;
        return reader.GetDouble(columnIndex);
    }
    #endregion

	#region GetDecimal/GetNullableDecimal
	public static decimal GetDecimal(IDataReader reader, string columnName)
	{
		return reader.GetDecimal(reader.GetOrdinal(columnName));
	}

	public static decimal GetDecimal(IDataReader reader, string columnName, decimal defaultValue)
	{
		return GetDecimal(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static decimal GetDecimal(IDataReader reader, int columnIndex, decimal defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetDecimal(columnIndex);
	}

	public static decimal? GetNullableDecimal(IDataReader reader, string columnName)
	{
		return GetNullableDecimal(reader, reader.GetOrdinal(columnName));
	}

	public static decimal? GetNullableDecimal(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetDecimal(columnIndex);
	}
	#endregion

	#region GetBoolean/GetNullableBoolean
	public static bool GetBoolean(IDataReader reader, string columnName)
	{
		return reader.GetBoolean(reader.GetOrdinal(columnName));
	}

	public static bool GetBoolean(IDataReader reader, string columnName, bool defaultValue)
	{
		return GetBoolean(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static bool GetBoolean(IDataReader reader, int columnIndex, bool defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetBoolean(columnIndex);
	}

	public static bool? GetNullableBoolean(IDataReader reader, string columnName)
	{
		return GetNullableBoolean(reader, reader.GetOrdinal(columnName));
	}

	public static bool? GetNullableBoolean(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetBoolean(columnIndex);
	}
	#endregion

	#region GetDateTime/GetNullableDateTime
	public static DateTime GetDateTime(IDataReader reader, string columnName)
	{
		return reader.GetDateTime(reader.GetOrdinal(columnName));
	}

	public static DateTime GetDateTime(IDataReader reader, string columnName, DateTime defaultValue)
	{
		return GetDateTime(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static DateTime GetDateTime(IDataReader reader, int columnIndex, DateTime defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetDateTime(columnIndex);
	}

	public static DateTime? GetNullableDateTime(IDataReader reader, string columnName)
	{
		return GetNullableDateTime(reader, reader.GetOrdinal(columnName));
	}

	public static DateTime? GetNullableDateTime(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetDateTime(columnIndex);
	}
	#endregion

	#region GetGuid/GetNullableGuid
	public static Guid GetGuid(IDataReader reader, string columnName)
	{
		return reader.GetGuid(reader.GetOrdinal(columnName));
	}

	public static Guid GetGuid(IDataReader reader, string columnName, Guid defaultValue)
	{
		return GetGuid(reader, reader.GetOrdinal(columnName), defaultValue);
	}

	public static Guid GetGuid(IDataReader reader, int columnIndex, Guid defaultValue)
	{
		if(reader.IsDBNull(columnIndex))
			return defaultValue;
		return reader.GetGuid(columnIndex);
	}

	public static Guid? GetNullableGuid(IDataReader reader, string columnName)
	{
		return GetNullableGuid(reader, reader.GetOrdinal(columnName));
	}

	public static Guid? GetNullableGuid(IDataReader reader, int columnIndex)
	{
		if(reader.IsDBNull(columnIndex))
			return null;
		return reader.GetGuid(columnIndex);
	}
	#endregion
}